package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Division;
import com.sl.au.entity.Hierarchy;

@Transactional(readOnly = true)
public interface DivisionRepository extends JpaRepository<Division, String> {

	@Query(" select a from Division a where a.divisionParent is not null order by a.divisionLevel.extended1 asc,a.divisionOrder asc")
	List<Division> findAllNodes();

	@Query(" select a from Division a where a.divisionParent is null and ( a.divisionName like %?1% or a.divisionIdentify like %?1% or a.divisionLevel.title like %?1%) ")
	Page<Division> getDivisionByKey(String key, Pageable pageable);

	@Query(" select count(*) from Division a where a.divisionParent is null and ( a.divisionName like %?1% or a.divisionIdentify like %?1% or a.divisionLevel.title like %?1%) ")
	long getDivisionByKeyCount(String key);

	@Query(" select a from Division a where a.divisionParent is null ")
	Page<Division> getDivision(Pageable pageable);

	@Query(" select count(*) from Division a where a.divisionParent is null ")
	long getDivisionCount();

	@Modifying
	@Query("update Division a set a.divisionParent = null where a.id=?1")
	int setDivision(String id);

	@Modifying
	@Query("update Division a set a.divisionParent = null where a.id in (?1)")
	int setDivision(String[] ids);

	@Query(" select IFNULL(max(a.divisionOrder),-2) + 2 from Division a where a.divisionParent = ?1 ")
	int getDivisionOrder(String parentId);

	@Query(" select a from Division a where a.divisionParent = ?1 order by a.divisionOrder asc")
	List<Division> getDivisionsByParent(String parentId);

	@Query(" select a from Division a where a.divisionParent='0'")
	List<Division> getParent();

	Division findTopByDivisionParentAndDivisionIdentify(String divisionParent, String divisionIdentify);

	Page<Division> findByDivisionParent(String divisionParent, Pageable pageable);

	@Query(" select a from Division a where a.divisionParent = ?1 and ( a.divisionName like %?2% or a.divisionIdentify like %?2% or a.divisionLevel.title like %?2% ) ")
	Page<Division> findByDivisionParentAndKey(String id, String key, Pageable pageable);

	@Query("select a from Division a where a.divisionParent = ?1")
	List<Division> findByParentId(String parentId);
}
